<?php
/**
 * Pipahr
 *
 * @author Wei Ma
 * @copyright 2013 pipahr.com
 *
 * @since   3.0
 */

class PInterviewHelper
{
   /**
    * Update jobseeker online interview completion & progress
    *
    * @param   integer  $id  Jobseeker id or Registered user id
    * @param   integer  $jobid  Jobposition id
    * @return  boolean
    *
    * @since   3.0
    */
   //skill level
   const SKILL_LEVEL_KNOW = 1;  //了解
   const SKILL_LEVEL_GENERALE = 2; //普通
   const SKILL_LEVEL_SKILLED = 3;  //熟练
   const SKILL_LEVEL_PROFICIENTE = 4;  //精通

   public static function updateInterviewProgress($requirement, $id = 0, $jobid = 0, $giveup = false) {
      $db = PFactory::getDbo();
      
      static $online_interview_weight_config = array(
               'interview'  => 50,
               'onlinetest' => 20,
               'persontest' => 10,
               'video'      => 20,
               '-persontest'=> 0
      );
      
      if (empty($requirement) || empty($id) || empty($jobid) || !array_key_exists($requirement, $online_interview_weight_config)) {
         return ;
      }     
      $by=is_array($jobid)?' in('.implode(',', $jobid).')':'='.intval($jobid);
      if ($requirement == 'persontest' || $requirement == '-persontest') {
         $query = "SELECT hj.id AS jobseeker_id, hj.online_interview_progress, jj.online_interview_requirements " .
                  " FROM *PREFIX*hr_jobseeker AS hj " .
                  " LEFT JOIN #__users AS u ON u.email=hj.email" .
                  " LEFT JOIN #__jobs_job AS jj ON jj.id=hj.job_id WHERE hj.company_id <> 0 AND u.id=" . intval($id) . " AND jj.id" . $by;
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $results = $db->loadObjectList();
      } else {
         $query = "SELECT hj.id AS jobseeker_id, hj.online_interview_progress, jj.online_interview_requirements " .
                  " FROM *PREFIX*hr_jobseeker AS hj " .
                  " LEFT JOIN #__jobs_job AS jj ON jj.id=hj.job_id WHERE hj.company_id <> 0 AND hj.id=" . intval($id) . " AND jj.id" . $by;
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $results = $db->loadObjectList();
      }
      
      if($results) {
         $value="";
         foreach ($results as $result){
         if(!empty($result->online_interview_requirements))
            $requirements = json_decode($result->online_interview_requirements, true);
         else 
            $requirements = array();
         
         if(!empty($result->online_interview_progress))
            $progress = json_decode($result->online_interview_progress, true);
         else
            $progress = array();

         // check if the job has the requirement indeed
         if ($requirement == 'interview' || (array_key_exists($requirement, $requirements) && $requirements[$requirement])) {
            if($giveup == false){
               $progress[$requirement] = $online_interview_weight_config[$requirement];
            }else{
               $progress[$requirement] = 0;
            }           
         }
         if($requirement == '-persontest'){
            unset($progress['persontest']);
         }
         $completion = 0;
         $totalweight = $online_interview_weight_config['interview'];  // interview weight, must have
         $progress_weight = 0;

         if(!empty($requirements)) {
            foreach($requirements as $job_requirement => $selected) {
               if (!array_key_exists($job_requirement, $online_interview_weight_config))
                  continue;
               
               if ($selected) {
                  $totalweight += $online_interview_weight_config[$job_requirement];
               }
            }
         }
          
         foreach($progress as $v) {
            $progress_weight += $v;
         }        
         if ($totalweight<=0) {
            $completion = 0;
         }else{
            $completion = round(($progress_weight/$totalweight), 2)*100;
         }   
         $value.="(".intval($result->jobseeker_id).",".$completion.",".$db->quote(json_encode($progress))."),"; 
         }
      }
     
      if(!empty($value)){
         $query = "INSERT INTO *PREFIX*hr_jobseeker(id,online_interview_completion,online_interview_progress)VALUES".rtrim($value, ",")." 
                ON DUPLICATE KEY UPDATE online_interview_completion=values(online_interview_completion),online_interview_progress=values(online_interview_progress)";        
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $db->execute();
      }
      CacheHelper::triggerRemove($id, array('*PREFIX*hr_jobseeker','*PREFIX*hr_jobseeker-'.$id));
      return true;
   }

   /**
    * Get jobseeker's interview progress array
    * You can check each progress by using isset
    *
    * @param  int $jobseeker_id jobseeker id
    * @return mixed  progress details array or false
    *
    * @since   3.0
    */
   public static function getInterviewProgress($jobseeker_id,$companyid=0) {
      if (empty($jobseeker_id))
         return false;

      $db = PFactory::getDbo();
      $query = "SELECT online_interview_progress FROM *PREFIX*hr_jobseeker WHERE id=".intval($jobseeker_id)." and company_id=".intval($companyid);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $result = $db->loadResult();

      return !empty($result) ? json_decode($result,true) : false;
   }

   /**
    * Update how many times that this sheet has been hited by jobseeker
    * @param  integer $sheetid  sheet's id
    * @return bool
    *
    * @since 3.1
    */
   public static function updateSheetHits($sheetid=0) {
      if (empty($sheetid))
         return false;

      $db = PFactory::getDbo();
      $query = "UPDATE #__jobtest_sheet SET hits=hits+1 WHERE id=".intval($sheetid);
      $db->setQuery($query);
      if (!$db->execute()) {
         return false;
      }
      return true;
   }

   /**
    * Get company's url
    *
    * @param  string  $sub_domain  company's sub_domain
    * @param  integer $company_id  company's id
    * @return mixed   link or false
    *
    * @since 3.1
    */
   public static function getCompanyUrl($sub_domain="", $company_id=0) {
      if (empty($company_id) && empty($sub_domain))
         return false;

      if (empty($sub_domain)) {
         $query = "SELECT sub_domain FROM #__jobs_employer WHERE user_id=" . $company_id;
         $db->setQuery($query);
         $sub_domain = $db->loadResult();
      }

      if (!empty($sub_domain)) {
         $homeLink = 'http://' . $sub_domain . '.'.PUtil::serverDomain();
      } elseif (!empty($company_id)) {
         $homeLink = 'http://' . $company_id . '.'.PUtil::serverDomain();
      } else {
         return false;
      }

      return $homeLink;
   }

   /**
    * function updateMatchDegree
    * @param  string  $token user's token
    * @param  int     $jobseeker_id jobseeker_id
    * @param  int     $job_id job_id
    * @return boolean
    */
   public static function updateMatchDegree($token, $jobseeker_id, $job_id) {
      $db = PFactory::getDbo();

      if (empty($job_id) || empty($jobseeker_id)) {
         $query = "SELECT jobseeker_id, job_id
            FROM *PREFIX*hr_jobseeker_token hjt
            LEFT JOIN *PREFIX*hr_jobseeker hj ON hj.id = hjt.jobseeker_id
            WHERE hjt.token=".$db->quote($token);
         $db->setQuery(PUtil::replaceOCPrefix($query));
         $info = $db->loadObject();
         if (empty($info)) {
            return false;
         }
         $jobseeker_id = $info->jobseeker_id;
         $job_id = $info->job_id;
      }

      // if we reach here job_id and jobseeker_id should be not null 
      if (empty($job_id) || empty($jobseeker_id)) {
         return false; 
      }
      
      $match_degree = 0;

      $basic_requirements_weight = 0;
      $skill_requirements_weight = 0;
      $lang_requirements_weight  = 0;
      $jobtest_requirements_weight = 0;

      $basic_DEGREE_weight = 30;
      $basic_INDUSTRY_weight = 60;
      $basic_SCHOOL_LEVEL_weight = 20;
      $basic_SALARY_weight = 50;
      $basic_LOCATION_weight = 50;
      $basic_WORK_EXP_weight = 60;
      $basic_SEX_weight = 20;
      $basic_AGE_weight = 20;
      $basic_ENTRY_DEADLINE_weight = 20;
      $basic_total_weight = 0;

      $basic_requirements_num = 0;
      $skill_requirements_num = 0;
      $lang_requirements_num = 0;
      $lang_requirements_satisfy_num = 0;

      // interview requirements match
      $query = "SELECT hci.expected_answer, hci.weight, hji.answer, hiq.id AS qid, hiq.alias, hiq.match_rule, hiq.default_weight
         FROM *PREFIX*hr_company_interview hci
         LEFT JOIN *PREFIX*hr_jobseeker_interview hji ON (hji.interview_id = hci.interview_id AND hji.jobseeker_id=".intval($jobseeker_id).")
         LEFT JOIN *PREFIX*hr_interview_questions hiq ON hiq.id = hci.interview_id
         WHERE hji.answer IS NOT NULL AND hci.expected_answer!='' AND hiq.alias != 'MARRIAGE' AND hiq.match_rule IS NOT NULL AND hci.job_id=".intval($job_id);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $requirements = $db->loadObjectList();

      // check if has jobtest sheet
      $query = "SELECT COUNT(*) FROM #__jobtest_jobsheet WHERE jobid=".intval($job_id);
      $db->setQuery($query);
      $has_jobtest = $db->loadResult();

      // default match details is empty since we collect the user's filtering information regardless of HR settings
      $match_degree_details = array();
      if ($requirements) {
         foreach ($requirements as $requirement) {
            switch ($requirement->alias) {
               default:
               case 'SKILL':
                  $skill_requirements_num++;
                  break;
               case 'LANG':
                  $lang_requirements_num++;
                  break;
               case 'DEGREE':
                  if (!empty($requirement->weight)) {
                     $basic_DEGREE_weight = $requirement->weight;
                  } elseif (!empty($requirement->default_weight)) {
                     $basic_DEGREE_weight = $requirement->default_weight;
                  }
                  $basic_total_weight += $basic_DEGREE_weight;
                  $basic_requirements_num++;
                  break;
               case 'INDUSTRY':
                  if (!empty($requirement->weight)) {
                     $basic_INDUSTRY_weight = $requirement->weight;
                  } elseif (!empty($requirement->default_weight)) {
                     $basic_INDUSTRY_weight = $requirement->default_weight;
                  }
                  $basic_total_weight += $basic_INDUSTRY_weight;
                  $basic_requirements_num++;
                  break;
               case 'SCHOOL_LEVEL':
                  if (!empty($requirement->weight)) {
                     $basic_SCHOOL_LEVEL_weight = $requirement->weight;
                  } elseif (!empty($requirement->default_weight)) {
                     $basic_SCHOOL_LEVEL_weight = $requirement->default_weight;
                  }
                  $basic_total_weight += $basic_SCHOOL_LEVEL_weight;
                  $basic_requirements_num++;
                  break;
               case 'SALARY':
                  if (!empty($requirement->weight)) {
                     $basic_SALARY_weight = $requirement->weight;
                  } elseif (!empty($requirement->default_weight)) {
                     $basic_SALARY_weight = $requirement->default_weight;
                  }
                  $basic_total_weight += $basic_SALARY_weight;
                  $basic_requirements_num++;
                  break;
               case 'LOCATION':
                  if (!empty($requirement->weight)) {
                     $basic_LOCATION_weight = $requirement->weight;
                  } elseif (!empty($requirement->default_weight)) {
                     $basic_LOCATION_weight = $requirement->default_weight;
                  }
                  $basic_total_weight += $basic_LOCATION_weight;
                  $basic_requirements_num++;
                  break;
               case 'WORK_EXP':
                  if (!empty($requirement->weight)) {
                     $basic_WORK_EXP_weight = $requirement->weight;
                  } elseif (!empty($requirement->default_weight)) {
                     $basic_WORK_EXP_weight = $requirement->default_weight;
                  }
                  $basic_total_weight += $basic_WORK_EXP_weight;
                  $basic_requirements_num++;
                  break;
               case 'SEX':
                  if (!empty($requirement->weight)) {
                     $basic_SEX_weight = $requirement->weight;
                  } elseif (!empty($requirement->default_weight)) {
                     $basic_SEX_weight = $requirement->default_weight;
                  }
                  $basic_total_weight += $basic_SEX_weight;
                  $basic_requirements_num++;
                  break;
               case 'AGE':
                  if (!empty($requirement->weight)) {
                     $basic_AGE_weight = $requirement->weight;
                  } elseif (!empty($requirement->default_weight)) {
                     $basic_AGE_weight = $requirement->default_weight;
                  }
                  $basic_total_weight += $basic_AGE_weight;
                  $basic_requirements_num++;
                  break;
               case 'ENTRY_DEADLINE':
                  if (!empty($requirement->weight)) {
                     $basic_ENTRY_DEADLINE_weight = $requirement->weight;
                  } elseif (!empty($requirement->default_weight)) {
                     $basic_ENTRY_DEADLINE_weight = $requirement->default_weight;
                  }
                  $basic_total_weight += $basic_ENTRY_DEADLINE_weight;
                  $basic_requirements_num++;
                  break;
            }
         }

         // calculate total weight
         if ($basic_requirements_num) {
            $basic_requirements_weight = 50;
         }

         if ($skill_requirements_num) {
            $skill_requirements_weight = 60;
         }

         if ($lang_requirements_num) {
            $lang_requirements_weight  = 10;
         }

         if ($has_jobtest) {
            $jobtest_requirements_weight = 30;
         }

         $total_requirements_weight = $basic_requirements_weight + $skill_requirements_weight + $lang_requirements_weight + $jobtest_requirements_weight;

         // not happen in most case, just in case ststem error
         if ($total_requirements_weight <= 0) {
            if ($jobtest_requirements_weight > 0) {
               // if error, we can only make jobtest match correct
               $total_requirements_weight = $jobtest_requirements_weight;
            } else {
               $total_requirements_weight = 1;
            }
         }

         $basic_requirements_rate = ($basic_requirements_weight/$total_requirements_weight);
         $skill_requirements_rate = ($skill_requirements_weight/$total_requirements_weight);
         $lang_requirements_rate = ($lang_requirements_weight/$total_requirements_weight);
         $jobtest_requirements_rate = ($jobtest_requirements_weight/$total_requirements_weight);

         foreach ($requirements as $requirement) {
            $small_match_degree = 0;
            switch ($requirement->alias) {
               default:
               case 'SKILL':
               	  $exp_small_match_degree = 0;
               	  $level_small_match_degree = 0;
                  $match_segments = (1/$skill_requirements_num)*$skill_requirements_rate;
                  if (is_numeric($requirement->expected_answer)) {
                     if (is_numeric($requirement->answer)){
                        $exp = intval($requirement->answer);
                     } else {
                        $answer = json_decode($requirement->answer);
                        $exp = intval($answer->exp);
                     }
                     if ($exp >= intval($requirement->expected_answer)) {
                        $small_match_degree = 1;
                        $match_degree += $match_segments;
                     } else {
                        if (intval($requirement->expected_answer) != 0) {
                           $diff = ceil((intval($requirement->expected_answer) - $exp)*10/intval($requirement->expected_answer));

                           switch ($diff) {
                              case '1':
                                 $small_match_degree = 0.9;
                                 break;
                              case '2':
                                 $small_match_degree = 0.7;
                                 break;
                              case '3':
                                 $small_match_degree = 0.5;
                                 break;
                              case '4':
                              case '5':
                                 $small_match_degree = 0.3;
                                 break;
                              default:
                                 break;
                           }

                           if ($small_match_degree > 0) {
                              $match_segments = $small_match_degree*$match_segments;
                              $match_degree += $match_segments;
                           }
                        }
                     }
                  }else{
                     if(is_numeric($requirement->answer) && $requirement->answer==0){
                        $exp = 0;
                        $level = 0;
                     }else{
                        $answer = json_decode($requirement->answer);
                        $exp = $answer->exp;
                        $level = $answer->level;
                     }

                     $expected_answer = json_decode($requirement->expected_answer);
                     $expected_exp = $expected_answer->exp;
                     $expected_level = $expected_answer->level;

                     if (intval($exp) >= intval($expected_exp)){
                        $exp_small_match_degree = 0.5;
                     }else{
                        if (intval($expected_exp) != 0) {
                           $diff = ceil((intval($expected_exp) - intval($exp))*10/intval($expected_exp));
                           switch ($diff) {
                              case '1':
                                 $exp_small_match_degree = 0.45;
                                 break;
                              case '2':
                                 $exp_small_match_degree = 0.35;
                                 break;
                              case '3':
                                 $exp_small_match_degree = 0.25;
                                 break;
                              case '4':
                              case '5':
                                 $exp_small_match_degree = 0.15;
                                 break;
                              default:
                                 break;
                           }
                        }
                     }

                     if(intval($level) >= intval($expected_level)){
                        $level_small_match_degree = 0.5;
                     }
                     $small_match_degree = $exp_small_match_degree+$level_small_match_degree;
                     $match_segments = $small_match_degree*$match_segments;
                     $match_degree += $match_segments;
                  }
                  break;
               case 'LANG':
                  if (intval($requirement->answer) >= intval($requirement->expected_answer)) {
                     $small_match_degree = 1;
                     $lang_requirements_satisfy_num++;
                  }
                  break;
               case 'DEGREE':
                  if (intval($requirement->answer) >= intval($requirement->expected_answer)) {
                     $small_match_degree = 1;
                     $match_degree += ($basic_DEGREE_weight/$basic_total_weight)*$basic_requirements_rate;
                  }
                  break;
               case 'INDUSTRY':
                  if (intval($requirement->answer) == intval($requirement->expected_answer)) {
                     $small_match_degree = 1;
                     $match_degree += ($basic_INDUSTRY_weight/$basic_total_weight)*$basic_requirements_rate;
                  }
                  break;
               case 'SCHOOL_LEVEL':
                  if (intval($requirement->answer) <= intval($requirement->expected_answer)) {
                     $small_match_degree = 1;
                     $match_degree += ($basic_SCHOOL_LEVEL_weight/$basic_total_weight)*$basic_requirements_rate;
                  }
                  break;
               case 'SALARY':
                  $match_segments = ($basic_SALARY_weight/$basic_total_weight)*$basic_requirements_rate;
                  if (intval($requirement->answer) <= intval($requirement->expected_answer)) {
                     $small_match_degree = 1;
                     $match_degree += $match_segments;
                  } else {
                     if (intval($requirement->expected_answer) != 0) {
                        /**
                         * $diff means the percentage that jobseeker SALARY answer higher than hr expected
                         * case 1 to 5 means 10% to 50%
                         */
                        $diff = ceil((intval($requirement->answer) - intval($requirement->expected_answer))*10/intval($requirement->expected_answer));

                        switch ($diff) {
                           case '1':
                              $small_match_degree = 0.9;
                              break;
                           case '2':
                              $small_match_degree = 0.7;
                              break;
                           case '3':
                              $small_match_degree = 0.5;
                              break;
                           case '4':
                           case '5':
                              $small_match_degree = 0.3;
                              break;
                           default:
                              break;
                        }

                        if ($small_match_degree > 0) {
                           $match_segments = $small_match_degree*$match_segments;
                           $match_degree += $match_segments;
                        }
                     }
                  }
                  break;
               case 'LOCATION':
                  if (intval($requirement->answer) == 1) {
                     $small_match_degree = 1;
                     $match_degree += ($basic_LOCATION_weight/$basic_total_weight)*$basic_requirements_rate;
                  }
                  break;
               case 'WORK_EXP':
                  $match_segments = ($basic_WORK_EXP_weight/$basic_total_weight)*$basic_requirements_rate;
                  if (intval($requirement->answer) >= intval($requirement->expected_answer)*12) {
                     $small_match_degree = 1;
                     $match_degree += $match_segments;
                  } else {
                     if (intval($requirement->expected_answer) != 0) {
                        /**
                         * $diff means the percentage that jobseeker WORK_EXP answer lower than hr expected
                         * case 1 to 5 means 10% to 50%
                         */
                        $diff = ceil((intval($requirement->expected_answer)*12 - intval($requirement->answer))*10/(intval($requirement->expected_answer)*12));

                        switch ($diff) {
                           case '1':
                              $small_match_degree = 0.9;
                              break;
                           case '2':
                              $small_match_degree = 0.7;
                              break;
                           case '3':
                              $small_match_degree = 0.5;
                              break;
                           case '4':
                           case '5':
                              $small_match_degree = 0.3;
                              break;
                           default:
                              break;
                        }

                        if ($small_match_degree > 0) {
                           $match_segments = $small_match_degree*$match_segments;
                           $match_degree += $match_segments;
                        }
                     }
                  }
                  break;
               case 'SEX':
                  if (intval($requirement->answer) == intval($requirement->expected_answer)) {
                     $small_match_degree = 1;
                     $match_degree += ($basic_SEX_weight/$basic_total_weight)*$basic_requirements_rate;
                  }
                  break;
               case 'AGE':
                  $ages = explode('-', $requirement->expected_answer);
                  if (count($ages) == 2) {
                     $match_segments = ($basic_AGE_weight/$basic_total_weight)*$basic_requirements_rate;
                     if (intval($requirement->answer) >= $ages[0] && intval($requirement->answer) <= $ages[1]) {
                        $small_match_degree = 1;
                        $match_degree += $match_segments;
                     } elseif (intval($requirement->answer) > $ages[1]) {
                        /**
                         * $diff means the years old jobseeker AGE answer higher than hr expected
                         * case 1 to 5 means 1 year old to 5 year old
                         */
                        $diff = ceil(intval($requirement->answer) - $ages[1]);

                        switch ($diff) {
                           case '1':
                           case '2':
                              $small_match_degree = 0.8;
                              break;
                           case '3':
                           case '4':
                           case '5':
                              $small_match_degree = 0.5;
                              break;
                           default:
                              break;
                        }

                        if ($small_match_degree > 0) {
                           $match_segments = $small_match_degree*$match_segments;
                           $match_degree += $match_segments;
                        }
                     }
                  }
                  break;
               case 'ENTRY_DEADLINE':
                  $match_segments = ($basic_ENTRY_DEADLINE_weight/$basic_total_weight)*$basic_requirements_rate;
                  if (intval($requirement->answer) <= intval($requirement->expected_answer)) {
                     $small_match_degree = 1;
                     $match_degree += $match_segments;
                  } else {
                     /**
                      * $diff means the option diff value that jobseeker ENTRY_DEADLINE answer longer than hr expected
                      * only diff is 1 we give 50% match, otherwise is 0
                      */
                     $diff = (intval($requirement->answer) - intval($requirement->expected_answer));

                     if (intval($requirement->answer) != 5 && $diff == 1) {
                        $small_match_degree = 0.5;
                     }

                     if ($small_match_degree > 0) {
                        $match_segments = $small_match_degree*$match_segments;
                        $match_degree += $match_segments;
                     }
                  }
                  break;
            }

            if ($small_match_degree < 0) {
               $small_match_degree = 0;
            }

            $match_degree_details[$requirement->qid] = round($small_match_degree, 2)*100;
         }

         if ($lang_requirements_num) {
            $match_degree += ($lang_requirements_satisfy_num/$lang_requirements_num)*$lang_requirements_rate;
         }

      }

      if ($has_jobtest) {
         // jobtest match
         $onlinetest_match_degree = 0;
         if (empty($requirements)) {
            $jobtest_requirements_rate = 1;
         }
         $query = "SELECT SUM(jr.score) AS score, js.totalscore AS totalscore
            FROM #__jobtest_result jr
            LEFT JOIN #__jobtest_sheet js ON js.id = jr.sheetid
            WHERE jr.sheetid=(SELECT sheetid FROM #__jobtest_result WHERE jobseeker_id=".intval($jobseeker_id)." ORDER BY id DESC LIMIT 1) AND jobseeker_id=".intval($jobseeker_id);
         $db->setQuery($query);
         $jobtest_result = $db->loadObject();
         if ($jobtest_result && $jobtest_result->score && $jobtest_result->totalscore) {
            $onlinetest_match_degree = ($jobtest_result->score/$jobtest_result->totalscore);
            $match_degree += $onlinetest_match_degree*$jobtest_requirements_rate;
         }
         $match_degree_details['onlinetest'] = round($onlinetest_match_degree, 2)*100;
      }

      // update resume match degree details
      $time = JFactory::getDate()->toSql();
      $query = "INSERT INTO *PREFIX*hr_jobseeker_match_details (jobseeker_id, jobid, details, lastupdated) VALUES
         (" . $jobseeker_id . ", " . $job_id . ", " . $db->quote(json_encode($match_degree_details)) . ", " . $db->quote($time) . ")
         ON DUPLICATE KEY UPDATE details=values(details), lastupdated=values(lastupdated)";
      $db->setQuery(PUtil::replaceOCPrefix($query));
      if (!$db->execute()) {
         return false;
      }

      if ($match_degree > 0) {
         $match_degree = round($match_degree, 2)*100;

         $update = "UPDATE *PREFIX*hr_jobseeker SET match_degree=".intval($match_degree).", date_processed=UTC_TIMESTAMP() WHERE id=".intval($jobseeker_id);
         $db->setQuery(PUtil::replaceOCPrefix($update));
         if (!$db->execute()) {
            return false;
         }
         CacheHelper::triggerRemove($jobseeker_id, array('*PREFIX*hr_jobseeker','*PREFIX*hr_jobseeker-'.$jobseeker_id));
         return $match_degree;
      }
      return true;
   } 
   
   public static function updatePersontestResult($jobseeker_id){     
      $db=JFactory::getDbo();
      $query="SELECT ju.id as user_id, ohj.job_id as jobid, pr.sheetid
              FROM *PREFIX*hr_jobseeker AS ohj  
              LEFT JOIN jos_users AS ju ON ju.email=ohj.email           
              LEFT JOIN jos_persontest_jobsheet AS pj ON pj.jobid=ohj.job_id 
              LEFT JOIN jos_persontest_result AS pr ON pr.user_id=ju.id AND pr.sheetid=pj.sheetid
              WHERE pr.result IS NOT NULL AND ohj.id=".intval($jobseeker_id);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $result = $db->loadObject();
      if(!$result)
         return false;
      if($result->sheetid) {
         //jobseeker has completed this persontest; update its status 
         self::updateInterviewProgress('persontest', $result->user_id, $result->jobid);
         return true; 
      } 
      return false;     
   }
  
   public static function isInviteToken($token){
      $db=JFactory::getDbo();
      $query="SELECT jj.auto_invite, hjt.auto_send
              FROM *PREFIX*hr_jobseeker_token AS hjt
              LEFT JOIN *PREFIX*hr_jobseeker AS hj ON hj.id=hjt.jobseeker_id
              LEFT JOIN #__jobs_job AS jj ON jj.id=hj.job_id
              WHERE hjt.token=" . $db->quote($token);
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $result = $db->loadObject();
      return !empty($result) && ($result->auto_invite || !$result->auto_send) ? true : false;
   }
   
   public static function getLatestToken($token,$short_token=false){
      if (empty($token))
         return null;

      $db=JFactory::getDbo();
      if ($short_token) {
         $where = "hjt.short_token=" . $db->quote($token);
      } else {
         $where = "hjt.token=" . $db->quote($token);
      }
      $query="SELECT hjt2.token,hjt.token AS token2 FROM *PREFIX*hr_jobseeker_token AS hjt 
              LEFT JOIN *PREFIX*hr_jobseeker_token AS hjt2 ON hjt2.jobseeker_id=hjt.jobseeker_id AND hjt2.disable=0 AND hjt2.token!=hjt.token
              WHERE ".$where;
      $db->setQuery(PUtil::replaceOCPrefix($query));
      $result = $db->loadObject();
      if (empty($result))
         return null;

      return empty($result->token) ? $result->token2 : $result->token;
   }
}
